Skip to main content

Workload Management And Resource Pools

Managing Resource Pools in SingleStore​

Overview​

As of April 2024, we utilize a unified SingleStore DB cluster for our various environments. This setup consolidates all databases and workloads, including those from analytics platforms like Grafana and Looker, and departments such as QA, Support, Customer Success, and Product Engineering, into a single resource pool.

This configuration can lead to resource contention; for instance, an analytics query for a low-priority Grafana report might delay critical operations such as a user opening a book. To manage and prioritize these workloads efficiently, we implement prioritized resource pools.

Resource Pool Configuration​

We have established three resource pools to segregate and prioritize system resources effectively:

  1. Top Priority Pool (Critical operations in production)

    • Specs:
      • MEMORY_PERCENTAGE = 100
      • SOFT_CPU_LIMIT_PERCENTAGE = 55
    • Users: farfalla

  2. High Priority Pool (Essential operations in production)

    • Specs:
      • MEMORY_PERCENTAGE = 90
      • SOFT_CPU_LIMIT_PERCENTAGE = 30
    • Users:
      • search
      • medusa
      • coniglio

  3. Default Priority Pool (General staging use, read-only and analytics in all environments)

    • Specs:
      • MEMORY_PERCENTAGE = 30
      • SOFT_CPU_LIMIT_PERCENTAGE = 10
      • QUERY_TIMEOUT = 40 (seconds)
      • MAX_CONCURRENCY = 40
    • Users:
      • global_readonly_qa
      • global_readonly_grafana
      • farfalla_staging
      • search_staging
      • medusa_staging
      • coniglio_staging
      • farfalla_readonly
      • farfalla_readonly_engineering
      • farfalla_readonly_operations
      • farfalla_readonly_cs
      • medusa_readonly
      • coniglio_readonly
      • farfalla_staging_readonly
      • medusa_staging_readonly
      • coniglio_staging_readonly

Note: SingleStore also provides a built-in default_pool, which we do not utilize as its parameters are non-configurable.

SQL Commands for Resource Pool Management​

-- Define resource pools:
CREATE RESOURCE POOL top_priority_pool WITH MEMORY_PERCENTAGE = 100, SOFT_CPU_LIMIT_PERCENTAGE = 55;
CREATE RESOURCE POOL high_priority_pool WITH MEMORY_PERCENTAGE = 90, SOFT_CPU_LIMIT_PERCENTAGE = 30;
CREATE RESOURCE POOL default_priority_pool WITH MEMORY_PERCENTAGE = 30, SOFT_CPU_LIMIT_PERCENTAGE = 10, QUERY_TIMEOUT = 40, MAX_CONCURRENCY = 40;

-- Drop resource pools:
DROP RESOURCE POOL top_priority_pool;
DROP RESOURCE POOL high_priority_pool;
DROP RESOURCE POOL default_priority_pool;

-- Assign default resource pools to users:
-- top_priority_pool
ALTER USER 'farfalla'@'%' SET DEFAULT RESOURCE POOL = top_priority_pool;
-- high_priority_pool
ALTER USER 'search'@'%' SET DEFAULT RESOURCE POOL = high_priority_pool;
-- default_priority_pool
ALTER USER 'global_readonly_qa'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'global_readonly_grafana'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'farfalla_staging'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'search_staging'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'medusa_staging'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'coniglio_staging'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'farfalla_readonly'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'farfalla_readonly_engineering'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'farfalla_readonly_operations'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'farfalla_readonly_cs'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'medusa_readonly'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'coniglio_readonly'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'farfalla_staging_readonly'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'medusa_staging_readonly'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'coniglio_staging_readonly'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
-- Users created via SingleStore Portal and used when querying through the portal
ALTER USER 'apaz@publica.la'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'fgilio@publica.la'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'imilano@publica.la'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'nmellusso@publica.la'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;
ALTER USER 'tbustamante@publica.la'@'%' SET DEFAULT RESOURCE POOL = default_priority_pool;

-- Misc

-- Check if CPU limit mode is set to SOFT or HARD
SELECT @@resource_governor_cpu_limit_mode;
-- List all resource pools and their settings
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_POOLS;
SHOW RESOURCE POOLS;
-- Show current resource pool for the session
SHOW VARIABLES like '%resource_pool%';
SELECT @@resource_pool;
-- Check stats of resource pools such as RUNNING_QUERIES and FINISHED_QUERIES
SELECT * FROM information_schema.MV_RESOURCE_POOL_STATUS;

References​

For further information on configuring and managing resource pools in SingleStore, refer to the following documentation:

X

Graph View